﻿/*
 *  Copyright 2009 Alessandro Pilotti
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation; either version 2.1 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA 
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;

namespace AlexPilotti.L2SQLDataModel.SchemaInfo
{
    public class MSSQLDBSchemaInfoFactory
    {
        public static IDBSchemaInfo CreateSchemaInfo(string connStr)
        {
            IDBSchemaInfo dbSchemaInfo = null;

            Version v = GetSQLServerVersion(connStr);
            if (v.Major == 8)
                dbSchemaInfo = new MSSQL2KDBSchemaInfo(connStr);
            else if (v.Major >= 9)
                dbSchemaInfo = new MSSQL05DBSchemaInfo(connStr);
            else
                throw new L2SQLDataModelException(string.Format("Unsupported SQL Server version: {0}", v));

            return dbSchemaInfo;
        }

        static Version GetSQLServerVersion(string connStr)
        {            
            Version v;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                string sql = "SELECT SERVERPROPERTY('productversion') as VersioNumber";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                    v = new Version((string)cmd.ExecuteScalar());
            }

            return v;
        }
    }


    public abstract class MSSQLBaseDBSchemaInfo : IDBSchemaInfo, IDisposable
    {
        protected const string procFuncTypesSql = "'P', 'PC', 'TF', 'FN', 'IF', 'FS', 'FT'";
        protected const string tableTypeSql = "'U'";
        protected const string viewTypeSql = "'V'";

        public event DBSchemaInfoErrorEventHandler HandleError;

        protected SqlConnection Conn { get; private set; }

        public MSSQLBaseDBSchemaInfo(string connStr)
        {
            Conn = new SqlConnection(connStr);
            Conn.Open();
        }

        public IList<TableInfo> GetTablesAndViewsInfo(IList<string> names, TableViewType type)
        {
            IList<TableInfo> tiList = FillTablesAndViewsColumnsInfo(names, GetTypeSql(type));
            FillUniqueConstraints(tiList);
            FillForeignKeyConstraints(tiList);

            return tiList;
        }

        /// <summary>
        /// </summary>
        /// <param name="type"></param>
        /// <returns>A list of strings coded in SQL</returns>
        private static string GetTypeSql(TableViewType type)
        {
            string sqlTypes = "";
            if ((type & TableViewType.Table) == TableViewType.Table)
                sqlTypes += tableTypeSql;
            if ((type & TableViewType.View) == TableViewType.View)
            {
                if (sqlTypes != "")
                    sqlTypes += ",";
                sqlTypes += viewTypeSql;
            }
            return sqlTypes;
        }

        private IList<TableInfo> FillTablesAndViewsColumnsInfo(IList<string> names, string sqlTypes)
        {
            IList<TableInfo> tiList = new List<TableInfo>();

            CreateTempTable(sqlTypes);

            DataTable dt = GetColumnsInfo();

            TableInfo ti = null;

            foreach (DataRow drColumn in dt.Rows)
            {
                string schemaName = (string)drColumn["schema_name"];
                string tableName = (string)drColumn["object_name"];
                string columnName = (string)drColumn["column_name"];
                int length = (int)drColumn["Length"];
                int precision = (int)drColumn["NumericPrecision"];
                int scale = (int)drColumn["NumericScale"];
                bool isNullable = (bool)drColumn["Nullable"];
                bool isIdentity = (bool)drColumn["Identity"];
                bool isComputed = (bool)drColumn["Computed"];
                string systemDbType = (string)drColumn["SystemType"];

                if (ti == null || (ti.SchemaName != schemaName || ti.Name != tableName))
                {
                    ti = new TableInfo();
                    ti.SchemaName = schemaName;
                    ti.Name = tableName;
                    tiList.Add(ti);
                }

                try
                {
                    ColumnInfo ci = CreateColumnInfo(columnName, length, precision, scale, isNullable, isIdentity, isComputed, systemDbType);
                    ti.Columns.Add(ci);
                }
                catch (Exception ex)
                {
                    string completeDBName = DynamicAssemblyUtility.CombineEscapedTSQLIdentifier(ti.SchemaName, ti.Name);
                    string msg = string.Format("Column \"{0}\" in table \"{1}\" has been skipped", columnName, completeDBName);
                    HandleWarningException(new L2SQLDataModelException(msg, ex));
                }
            }

            return tiList;
        }

        private static ColumnInfo CreateColumnInfo(string columnName, int length, int precision, int scale, bool isNullable, bool isIdentity, bool isComputed, string systemDbType)
        {
            SqlDbType sqlDbType = GetSqlDbType(systemDbType);

            ColumnInfo ci = new ColumnInfo()
            {
                Name = columnName,
                DataType = SystemTypeFromSqlDbType(sqlDbType),
                AllowNull = isNullable,
                DBType = sqlDbType.ToString(),
                DBTypeComplete = GetCompleteDbType(sqlDbType, length, precision, scale, isNullable, isIdentity),
                IsAutoInc = isIdentity,
                IsComputed = isComputed,
                Length = length,
                Precision = precision,
                Scale = scale
            };
            return ci;
        }

        private static SqlDbType GetSqlDbType(string systemDbType)
        {
            SqlDbType sqlDbType;
            try
            {
                // Numeric is just an alias to decimal, but it's not yet available in SqlDbType.
                if (systemDbType == "numeric")
                    sqlDbType = SqlDbType.Decimal;
                else if (systemDbType == "sql_variant")
                    sqlDbType = SqlDbType.Variant;
                else if (systemDbType == "hierarchyid")
                    sqlDbType = SqlDbType.Udt;
                else if (systemDbType == "geography")
                    sqlDbType = SqlDbType.Udt;
                else
                    sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), systemDbType, true);
            }
            catch (ArgumentException ex)
            {
                throw new L2SQLDataModelException("Unsupported MS SQL Server data type: " + systemDbType, ex);
            }
            return sqlDbType;
        }

        public IList<ProcedureInfo> GetProceduresInfo(IList<string> names)
        {
            IList<ProcedureInfo> piList = FillProceduresParametersInfo();
            FillFunctionsReturnColumnsInfo(piList);
            GetProceduresReturnSchema(piList);

            return piList;
        }

        private void GetProceduresReturnSchema(IList<ProcedureInfo> piList)
        {
            foreach(ProcedureInfo procInfo in piList)
                if (procInfo.Type == ProcedureType.Procedure || procInfo.Type == ProcedureType.CLRProcedure)
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = Conn;
                        cmd.CommandText = DynamicAssemblyUtility.CombineEscapedTSQLIdentifier(procInfo.SchemaName, procInfo.Name);
                        cmd.CommandType = CommandType.StoredProcedure;

                        foreach (ParameterInfo paramInfo in procInfo.Parameters)
                        {
                            SqlParameter param = cmd.CreateParameter();
                            cmd.Parameters.Add(param);

                            param.Direction = GetADONetParameterDirection(paramInfo.ParameterDir);
                            param.ParameterName = paramInfo.Name;
                            param.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), paramInfo.DBType);
                            param.Size = paramInfo.Length;

                            //param.Scale = (byte)paramInfo.Scale;
                            //param.Precision = (byte)paramInfo.Precision;                       
                        }

                        // FillSchema avoids calling the procedure directly, it just returns the result set(s) schema with: 
                        // SET NO_BROWSETABLE ON; SET FMTONLY ON;
                        // exec [dbo].[sp_Test]
                        // SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
                        DataSet ds = new DataSet();
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                            da.FillSchema(ds, SchemaType.Source);

                        // There are more tables in case of multiple result sets 
                        foreach (DataTable dt in ds.Tables)
                            if (dt.Columns.Count > 0)
                            {
                                TableInfo ti = new TableInfo();
                                procInfo.ReturnTables.Add(ti);

                                foreach (DataColumn dc in dt.Columns)
                                    try
                                    {
                                        SqlDbType sqlDbType = DynamicAssemblyUtility.GetSqlDbTypeFromDataColumn(dc);

                                        ColumnInfo ci = new ColumnInfo()
                                        {
                                            Name = dc.ColumnName,
                                            DataType = dc.DataType,
                                            AllowNull = dc.AllowDBNull,
                                            DBType = sqlDbType.ToString(),
                                            // Set some default scale / precision
                                            DBTypeComplete = GetCompleteDbType(sqlDbType, dc.MaxLength, 38, 10, dc.AllowDBNull, false),
                                            Length = dc.MaxLength,
                                            //IsAutoInc = dc.AutoIncrement
                                            IsAutoInc = false
                                        };
                                        ti.Columns.Add(ci);
                                    }
                                    catch (Exception ex)
                                    {
                                        string completeDBName = DynamicAssemblyUtility.CombineEscapedTSQLIdentifier(procInfo.SchemaName, procInfo.Name);
                                        string msg = string.Format("Column \"{0}\" returned by procedure \"{1}\" has been skipped", dc.ColumnName, completeDBName);
                                        HandleWarningException(new L2SQLDataModelException(msg, ex));
                                    }
                            }
                    }
                }
        }

        private static ParameterDirection GetADONetParameterDirection(ParameterDir paramType)
        {
            ParameterDirection pd = ParameterDirection.Input;
            switch (paramType)
            {
                case ParameterDir.Input:
                    pd = ParameterDirection.Input;
                    break;
                case ParameterDir.Output:
                    pd = ParameterDirection.Output;
                    break;
                case ParameterDir.InputOutput:
                    pd = ParameterDirection.InputOutput;
                    break;
                case ParameterDir.Return:
                    pd = ParameterDirection.ReturnValue;
                    break;
            }

            return pd;
        }


        private IList<ProcedureInfo> FillProceduresParametersInfo()
        {
            IList<ProcedureInfo> piList = new List<ProcedureInfo>();

            CreateTempTable(procFuncTypesSql);

            ProcedureInfo pi = null;

            DataTable dt = GetParametersInfo();
            foreach (DataRow drParam in dt.Rows)
            {
                string schemaName = (string)drParam["schema_name"];
                string objectName = (string)drParam["object_name"];
                string objectType = (string)drParam["object_type"];

                if (pi == null || (pi.SchemaName != schemaName || pi.Name != objectName))
                {
                    pi = new ProcedureInfo();
                    pi.Type = GetProcedureType(objectType);
                    pi.SchemaName = schemaName;
                    pi.Name = objectName;
                    piList.Add(pi);
                }

                // parameter_name is null in case of procedures / functions without parameters (as returned from a left outer join)
                if (!(drParam["parameter_name"] is DBNull))
                    try
                    {
                        pi.Parameters.Add(CreateParameterInfo(pi, drParam));
                    }
                    catch (Exception ex)
                    {
                        // Skip this procedure
                        piList.Remove(pi);

                        string paramName = (string)drParam["parameter_name"];
                        string completeDBName = DynamicAssemblyUtility.CombineEscapedTSQLIdentifier(pi.SchemaName, pi.Name);

                        string msg = string.Format("Procedure or function \"{0}\" has been skipped because parameter \"{1}\" could not be handled", 
                                                   completeDBName, paramName);
                        HandleWarningException(new L2SQLDataModelException(msg, ex));
                    }
            }

            return piList;
        }

        private static ParameterInfo CreateParameterInfo(ProcedureInfo pi, DataRow drParam)
        {
            string paramName = (string)drParam["parameter_name"];
            bool isOutputParameter = (bool)drParam["IsOutputParameter"];
            int length = (int)drParam["Length"];
            int precision = (int)drParam["NumericPrecision"];
            int scale = (int)drParam["NumericScale"];
            string systemDbType = (string)drParam["SystemType"];

            SqlDbType sqlDbType = GetSqlDbType(systemDbType);

            ParameterDir paramType;
            if (!isOutputParameter)
                paramType = ParameterDir.Input;
            else
                if (paramName.Length == 0)
                    paramType = ParameterDir.Return;
                else
                    paramType = ParameterDir.Output;

            ParameterInfo param = new ParameterInfo()
            {
                ParameterDir = paramType,
                Name = paramName,
                DBType = sqlDbType.ToString(),
                DBTypeComplete = GetCompleteDbType(sqlDbType, length, precision, scale, true, false),
                DataType = SystemTypeFromSqlDbType(sqlDbType),
                Length = length,
                Precision = precision,
                Scale = scale
            };

            return param;
        }

        private static ProcedureType GetProcedureType(string objectType)
        {
            ProcedureType type;
            switch (objectType.Trim())
            {
                case "FN":
                    type = ProcedureType.ScalarFunction;
                    break;
                case "FS":
                    type = ProcedureType.CLRScalarFunction;
                    break;
                case "FT":
                    type = ProcedureType.CLRTableFunction;
                    break;
                case "IF":
                    type = ProcedureType.InlineTableFunction;
                    break;
                case "P":
                    type = ProcedureType.Procedure;
                    break;
                case "PC":
                    type = ProcedureType.CLRProcedure;
                    break;
                case "TF":
                    type = ProcedureType.TableFunction;
                    break;
                default:
                    throw new L2SQLDataModelException(string.Format("Invalid procedure type: {0}", objectType));
            }

            return type;
        }

        private void FillFunctionsReturnColumnsInfo(IList<ProcedureInfo> piList)
        {
            ProcedureInfo pi = null;
            TableInfo ti = null; 

            DataTable dt = GetColumnsInfo();
            foreach (DataRow drColumn in dt.Rows)
            {
                string schemaName = (string)drColumn["schema_name"];
                string objectName = (string)drColumn["object_name"];
                string columnName = (string)drColumn["column_name"];
                int length = (int)drColumn["Length"];
                int precision = (int)drColumn["NumericPrecision"];
                int scale = (int)drColumn["NumericScale"];
                bool isNullable = (bool)drColumn["Nullable"];
                bool isIdentity = (bool)drColumn["Identity"];
                bool isComputed = (bool)drColumn["Computed"];
                string systemDbType = (string)drColumn["SystemType"];

                if (pi == null || (pi.SchemaName != schemaName || pi.Name != objectName))
                {
                    pi = (from p in piList where p.SchemaName == schemaName && p.Name == objectName select p).Single();
                    ti = new TableInfo();
                    pi.ReturnTables.Add(ti);
                }

                ColumnInfo ci = CreateColumnInfo(columnName, length, precision, scale, isNullable, isIdentity, isComputed, systemDbType);
                ti.Columns.Add(ci);
            }
        }

        private static string GetCompleteDbType(SqlDbType sqlDbType, int length, int precision, int scale, bool isNullable, bool isIdentity)
        {
            string completeDbType = sqlDbType.ToString();
            switch (sqlDbType)
            {
                case SqlDbType.Decimal:
                case SqlDbType.Money:
                    completeDbType += string.Format("({0},{1})", precision, scale);
                    break;
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.VarChar:
                case SqlDbType.NVarChar:
                case SqlDbType.VarBinary:
                    completeDbType += string.Format("({0})", length == -1 ? "max" : length.ToString());
                    break;
            }

            if (isIdentity)
                completeDbType += " IDENTITY";
            if (!isNullable)
                completeDbType += " NOT NULL";

            return completeDbType;
        }


        /// <summary>
        /// See http://msdn.microsoft.com/en-us/library/ms131092.aspx
        /// </summary>
        /// <param name="sqlDbType"></param>
        /// <returns></returns>
        private static Type SystemTypeFromSqlDbType(SqlDbType sqlDbType)
        {
            Type t = null;

            switch (sqlDbType)
            {
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.VarChar:
                case SqlDbType.Xml:
                    t = typeof(string);
                    break;
                case SqlDbType.BigInt:
                    t = typeof(Int64);
                    break;
                case SqlDbType.Binary:
                case SqlDbType.Image:
                case SqlDbType.VarBinary:
                case SqlDbType.Timestamp:
                    t = typeof(byte[]);
                    break;
                case SqlDbType.Bit:
                    t = typeof(bool);
                    break;
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.SmallDateTime:
                    t = typeof(DateTime);
                    break;
                case SqlDbType.Time:
                    t = typeof(TimeSpan);
                    break;
                case SqlDbType.DateTimeOffset:
                    t = typeof(DateTimeOffset);
                    break;
                case SqlDbType.Decimal:
                case SqlDbType.Money:
                case SqlDbType.SmallMoney:                
                    t = typeof(Decimal);
                    break;
                case SqlDbType.Float:
                    t = typeof(double);
                    break;
                case SqlDbType.Int:
                    t = typeof(int);
                    break;
                case SqlDbType.Real:
                    t = typeof(float);
                    break;
                case SqlDbType.SmallInt:
                    t = typeof(short);
                    break;
                case SqlDbType.UniqueIdentifier:
                    t = typeof(Guid);
                    break;
                case SqlDbType.TinyInt:
                    t = typeof(byte);
                    break;
                case SqlDbType.Variant:
                    t = typeof(object);
                    break;
                case SqlDbType.Udt:
                    t = typeof(object);
                    break;
                default:
                    throw new L2SQLDataModelException("Unsupported MS SQL Server data type: " + sqlDbType.ToString());
            }

            return t;
        }

        private void FillForeignKeyConstraints(IList<TableInfo> tiList)
        {
            DataTable dtFk = GetForeignKeyConstraints();
            TableInfo tiFk = null;
            TableInfo tiPk = null;
            ForeignKeyConstraintInfo fkCi = null;

            foreach (DataRow drFk in dtFk.Rows)
            {
                string fkSchemaName = (string)drFk["fkSchema"];
                string fkTableName = (string)drFk["fkTable"];
                string pkSchemaName = (string)drFk["pkSchema"];
                string pkTableName = (string)drFk["pkTable"];
                string fkName = (string)drFk["CONSTRAINT_NAME"];
                string pkName = (string)drFk["UNIQUE_CONSTRAINT_NAME"];
                string fkColumnName = (string)drFk["fkColumn"];

                if (tiFk == null || tiFk.SchemaName != fkSchemaName || tiFk.Name != fkTableName)
                    foreach (TableInfo ti in tiList)
                        if (ti.SchemaName == fkSchemaName && ti.Name == fkTableName)
                        {
                            tiFk = ti;
                            break;
                        }

                if (fkCi == null || fkCi.Name != fkName)
                {
                    fkCi = new ForeignKeyConstraintInfo();
                    fkCi.Name = fkName;
                    tiFk.Constraints.Add(fkCi);

                    if (tiPk == null || tiPk.SchemaName != pkSchemaName || tiPk.Name != pkTableName)
                        foreach (TableInfo ti in tiList)
                            if (ti.SchemaName == pkSchemaName && ti.Name == pkTableName)
                            {
                                tiPk = ti;
                                break;
                            }

                    fkCi.TargetTableInfo = tiPk;

                    foreach (ConstraintInfo ci in tiPk.Constraints)
                        if (ci.Name == pkName)
                        {
                            fkCi.TargetConstraint = ci as UniqueConstraintInfo;
                            break;
                        }
                }

                fkCi.ColumnNames.Add(fkColumnName);
            }
        }

        private void FillUniqueConstraints(IList<TableInfo> tiList)
        {
            DataTable dtPk = GetUniqueConstraints();
            TableInfo tiPk = null;
            UniqueConstraintInfo uCi = null;
            foreach (DataRow drPk in dtPk.Rows)
            {
                string constraintName = (string)drPk["CONSTRAINT_NAME"];
                string schemaName = (string)drPk["TABLE_SCHEMA"];
                string tableName = (string)drPk["TABLE_NAME"];
                string columnName = (string)drPk["COLUMN_NAME"];
                string constraintType = (string)drPk["CONSTRAINT_TYPE"];

                if (tiPk == null || tiPk.SchemaName != schemaName || tiPk.Name != tableName)
                    foreach (TableInfo ti in tiList)
                        if (ti.SchemaName == schemaName && ti.Name == tableName)
                        {
                            tiPk = ti;
                            uCi = null;
                            break;
                        }

                if (uCi == null || uCi.Name != constraintName)
                {
                    uCi = new UniqueConstraintInfo();
                    uCi.Name = constraintName;
                    tiPk.Constraints.Add(uCi);

                    uCi.IsPrimaryKey = (constraintType == "PRIMARY KEY");
                }

                uCi.ColumnNames.Add(columnName);
            }
        }

        /// <summary>
        /// Returns both PK and UNIQUE constraints
        /// </summary>
        /// <returns></returns>
        protected virtual DataTable GetUniqueConstraints()
        {
            string sql = "select " +
                        "tc.CONSTRAINT_SCHEMA, " +
                        "tc.CONSTRAINT_NAME, " +
                        "keycol.ORDINAL_POSITION, " +
                        "keycol.TABLE_SCHEMA, " +
                        "keycol.TABLE_NAME, " +
                        "keycol.COLUMN_NAME, " +
                        "tc.CONSTRAINT_TYPE " +
                        "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc, " +
                        "INFORMATION_SCHEMA.KEY_COLUMN_USAGE as keycol, " +
                        "#Objects as tb " +
                        "where tc.CONSTRAINT_SCHEMA = keycol.CONSTRAINT_SCHEMA and " +
                        "tc.CONSTRAINT_NAME = keycol.CONSTRAINT_NAME and " +
                        "tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') and " +
                        "tc.TABLE_SCHEMA = tb.schema_name and " +
                        "tc.TABLE_NAME = tb.name and " +
                        "ISNULL(OBJECTPROPERTY(OBJECT_ID(tc.TABLE_NAME), 'IsMSShipped'), 0) = 0 and " +
                        "tb.type in (" + tableTypeSql + ") " +
                        "order by  " +
                        "tb.schema_name, " +
                        "tb.name, " +
                        "tc.CONSTRAINT_SCHEMA, " +
                        "tc.CONSTRAINT_NAME, " +
                        "keycol.ORDINAL_POSITION";

            DataTable dt = new DataTable();

            using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
                da.Fill(dt);

            return dt;
        }

        protected virtual DataTable GetForeignKeyConstraints()
        {
            string sql = "select " +
                        "rc.CONSTRAINT_SCHEMA, " +
                        "rc.CONSTRAINT_NAME, " +
                        "pkcol.ORDINAL_POSITION, " +
/*
                        "(select count(*) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc " +
                        "where rc.CONSTRAINT_SCHEMA = kc.CONSTRAINT_SCHEMA and " +
                        "rc.CONSTRAINT_NAME = kc.CONSTRAINT_NAME and " +
                        "ISNULL(OBJECTPROPERTY(OBJECT_ID(kc.CONSTRAINT_NAME), 'IsMSShipped'), 0) = 0) " + 
                        "as COUNT, " +
 */ 
                        "fkcol.TABLE_SCHEMA as fkSchema, " +
                        "fkcol.TABLE_NAME as fkTable, " +
                        "fkcol.COLUMN_NAME as fkColumn, " +
                        "pkcol.TABLE_SCHEMA as pkSchema, " +
                        "pkcol.TABLE_NAME as pkTable, " +
                        "pkcol.COLUMN_NAME as pkColumn, " +
                        "rc.UNIQUE_CONSTRAINT_NAME, " + 
                        "rc.UPDATE_RULE, " +
                        "rc.DELETE_RULE " +
                        "from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc, " +
                        "INFORMATION_SCHEMA.KEY_COLUMN_USAGE as pkcol, " +
                        "INFORMATION_SCHEMA.KEY_COLUMN_USAGE as fkcol, " +
                        "#Objects as tb " +
                        "where rc.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA and " +
                        "rc.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME and " +
                        "rc.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA and " +
                        "rc.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME and " +
                        "pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION and " +
                        "ISNULL(OBJECTPROPERTY(OBJECT_ID(rc.CONSTRAINT_NAME), 'IsMSShipped'), 0) = 0 and  " +
                        "fkcol.TABLE_SCHEMA = tb.schema_name and " +
                        "fkcol.TABLE_NAME = tb.name and " +
                        "tb.type in (" + tableTypeSql + ") " +
                        "order by  " +
                        "tb.schema_name, " +
                        "tb.name, " +
                        "rc.CONSTRAINT_SCHEMA, " +
                        "rc.CONSTRAINT_NAME, " +
                        "pkcol.ORDINAL_POSITION";

            DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
                da.Fill(dt);

            return dt;
        }

        protected abstract string GetCreateTempTableSql(string objectTypesSql);

        protected virtual void CreateTempTable(string objectTypesSql)
        {
            string sql = @"if OBJECT_ID('tempdb..#Objects') IS NOT NULL DROP TABLE #Objects;";

            sql += GetCreateTempTableSql(objectTypesSql);

            using (SqlCommand cmd = new SqlCommand(sql, Conn))
                cmd.ExecuteNonQuery();
        }

        protected virtual DataTable GetParametersInfo()
        {
            string sql = GetParametersInfoSql();
            DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
                da.Fill(dt);

            return dt;
        }

        protected abstract string GetParametersInfoSql();

        protected virtual DataTable GetColumnsInfo()
        {
            string sql = GetColumnsInfoSql();
            DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
                da.Fill(dt);

            return dt;
        }

        protected abstract string GetColumnsInfoSql();

        /// <summary>
        /// Handle non fatal errors.
        /// </summary>
        /// <param name="ex"></param>
        private void HandleWarningException(Exception ex)
        {
            if (HandleError != null)
                HandleError(this, new DBSchemaInfoErrorEventArgs(ex));
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (Conn != null)
                Conn.Dispose();
        }

        #endregion
    }
}
